/**
	AUTHOR		: GANDHI
	DATE		: 05 SEP, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET RMA WAFERS DETAILS BASED ON SEARCH CRITERIA. 

	
	EXEC SP_RMA_WAFERS_REPORT 1374,'145','0','0','0','scrap'
	SP_HELP ENGINEERING_LOTS
*/

 
 ALTER   PROC SP_RMA_WAFERS_REPORT
 
 @PROCESSSTEP_ID BIGINT,
 @BASEPRODUCT_ID INT,
 @DATE_FROM VARCHAR (20),
 @DATE_TO VARCHAR (20),
 @DEFAULT_PROMPT VARCHAR(20),
 @SEL_CATEGORY	VARCHAR(20)
 
 AS
 BEGIN

	DECLARE @FINAL_RESULTS TABLE(PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50),LOT_ID INT,WAFER_ID INT,LOT_NUMBER VARCHAR(50),PROCESSSTEP_ID BIGINT,DATE_IN DATETIME,DATE_OUT DATETIME,BIN1 INT,BIN2 INT,BIN3 INT,BIN4 INT,BIN5 INT,CATEGORY VARCHAR(50),RMA_LIMIT BIT,MRB_LIMIT BIT,COMMENTS VARCHAR(250) )
	

		IF LTRIM(RTRIM(@PROCESSSTEP_ID)) IS NULL OR LTRIM(RTRIM(@PROCESSSTEP_ID)) = '' OR LTRIM(RTRIM(@PROCESSSTEP_ID)) = -1
		BEGIN
			SET @PROCESSSTEP_ID = 0
		END
		IF LTRIM(RTRIM(@BASEPRODUCT_ID)) IS NULL OR LTRIM(RTRIM(@BASEPRODUCT_ID)) = '' OR LTRIM(RTRIM(@BASEPRODUCT_ID)) = -1
		BEGIN
			SET @BASEPRODUCT_ID = 0
		END
  		IF LTRIM(RTRIM(@DATE_FROM)) IS NULL OR LTRIM(RTRIM(@DATE_FROM)) = '' OR LTRIM(RTRIM(@DATE_FROM)) = '-1'
		BEGIN
			SET @DATE_FROM = '0'
		END
		IF LTRIM(RTRIM(@DATE_TO)) IS NULL OR LTRIM(RTRIM(@DATE_TO)) = '' OR LTRIM(RTRIM(@DATE_TO)) = '-1'
		BEGIN
			SET @DATE_TO = '0'
		END

		DECLARE @SQLSTRING NVARCHAR(3000)
		DECLARE @PARAMDEFINITION NVARCHAR(500)
		DECLARE @MIN_DATE DATETIME
		DECLARE @MAX_DATE DATETIME

		IF @DATE_FROM = '0' OR @DATE_TO = '0'
		BEGIN
-- 			SELECT @MIN_DATE =  ISNULL( MIN(SL_DATE_OUT), MIN(SLH.TIMESTAMP) ), @MAX_DATE = ISNULL( MAX(SL_DATE_OUT), MAX(SLH.TIMESTAMP) )
-- 			FROM INFORMATIVE_BINNING IB,SORT_LOT_HISTORY SLH,LOT_TABLE LT,PRODUCT P
-- 			WHERE IB.LOT_ID = SLH.LOT_ID
-- 			AND IB.LOT_ID = LT.LOT_ID
-- 			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
--  			AND IB.RMA_LIMIT = 0 AND SLH.TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM SORT_LOT_HISTORY WHERE LOT_ID = LT.LOT_ID)
			SELECT @MIN_DATE = MIN( ISNULL( SL_DATE_OUT,TIMESTAMP ) ), @MAX_DATE = MAX( ISNULL( SL_DATE_OUT,TIMESTAMP ) ) FROM SORT_LOT_HISTORY
		END
		
		SET @SQLSTRING = N'DECLARE RMA_WAFERS CURSOR FOR SELECT P.PART_NUMBER,( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
 			 	P.P_PARENT_PRODUCT_ID ) AS BASE_PRODUCT,
 				IB.LOT_ID, IB.WAFER_ID, LT.LOT_NUMBER, LT.PROCESSSTEP_ID, ISNULL( SL_DATE_IN, SLH.TIMESTAMP ) SL_DATE_IN, ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) SL_DATE_OUT,
				ISNULL(IB.BIN_1,0) BIN1, ISNULL(IB.BIN_2,0) BIN2, ISNULL(IB.BIN_3,0) BIN3, ISNULL(IB.BIN_4,0) BIN4, ISNULL(IB.BIN_5,0) BIN5, IB.RMA_LIMIT, IB.MRB_LIMIT
 				FROM INFORMATIVE_BINNING IB,SORT_LOT_HISTORY SLH,LOT_TABLE LT,PRODUCT P
 				WHERE IB.LOT_ID = SLH.LOT_ID
				AND IB.LOT_ID = LT.LOT_ID
 				AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
 				AND (IB.RMA_LIMIT = 0 OR(IB.RMA_LIMIT = 1 AND IB.MRB_LIMIT = 0 )) AND SLH.TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM SORT_LOT_HISTORY WHERE LOT_ID = LT.LOT_ID)
				AND (ISNULL(IB.BIN_1,0) <> 0 OR ISNULL(IB.BIN_2,0) <> 0 OR ISNULL(IB.BIN_3,0) <> 0 OR ISNULL(IB.BIN_4,0) <> 0 OR ISNULL(IB.BIN_5,0) <> 0)'

		SET @PARAMDEFINITION = N'@PROCESSSTEP_ID BIGINT, @BASEPRODUCT_ID INT, @DATE_FROM VARCHAR(50), @DATE_TO VARCHAR(50), @MIN_DATE DATETIME, @MAX_DATE DATETIME'
		
		IF @PROCESSSTEP_ID <> 0
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID '
		END
		
		IF @BASEPRODUCT_ID <> 0
		BEGIN
			IF @PROCESSSTEP_ID = 0
			BEGIN
				SET @SQLSTRING = @SQLSTRING + ' AND P.P_PARENT_PRODUCT_ID  = @BASEPRODUCT_ID '
			END
		END
		
		IF @DATE_FROM <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) >= CONVERT(DATETIME,@DATE_FROM) '
		END
		ELSE
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) >= @MIN_DATE '
		END	
		
		IF @DATE_TO <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) <= CONVERT(DATETIME,@DATE_TO) '
		END
		ELSE
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) <= @MAX_DATE '
		END
		-- TO PULL LOTS FROM INFORMATIVE BINNING AND ENGINEERING LOTS 
		SET @SQLSTRING = @SQLSTRING +'UNION SELECT P.PART_NUMBER,( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
							P.P_PARENT_PRODUCT_ID ) AS BASE_PRODUCT,
							IB.LOT_ID, IB.WAFER_ID, LT.LOT_NUMBER, LT.PROCESSSTEP_ID,
							ISNULL( SL_DATE_IN, SLH.TIMESTAMP ) SL_DATE_IN, ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) SL_DATE_OUT,
							ISNULL(EL.BIN_1,0) BIN1, ISNULL(EL.BIN_2,0) BIN2, ISNULL(EL.BIN_3,0) BIN3, ISNULL(EL.BIN_4,0) BIN4, ISNULL(EL.BIN_5,0) BIN5, IB.RMA_LIMIT, IB.MRB_LIMIT
							FROM INFORMATIVE_BINNING IB ,ENGINEERING_LOTS EL ,SORT_LOT_HISTORY SLH,LOT_TABLE LT,PRODUCT P 
							WHERE IB.LOT_ID = EL.LOT_ID AND IB.WAFER_ID = EL.WAFER_ID 
							AND IB.LOT_ID = SLH.LOT_ID
							AND IB.LOT_ID = LT.LOT_ID
							AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
							AND ( IB.RMA_LIMIT = 1 AND IB.MRB_LIMIT = 1 )
							AND (ISNULL(EL.BIN_1,0) <> 0 OR ISNULL(EL.BIN_2,0) <> 0 OR ISNULL(EL.BIN_3,0) <> 0 OR ISNULL(EL.BIN_4,0) <> 0 OR ISNULL(EL.BIN_5,0) <> 0)' 		
		
		IF @PROCESSSTEP_ID <> 0
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID '
		END
		
		IF @BASEPRODUCT_ID <> 0
		BEGIN
			IF @PROCESSSTEP_ID = 0
			BEGIN
				SET @SQLSTRING = @SQLSTRING + ' AND P.P_PARENT_PRODUCT_ID  = @BASEPRODUCT_ID '
			END
		END
		
		IF @DATE_FROM <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) >= CONVERT(DATETIME,@DATE_FROM) '
		END
		ELSE
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) >= @MIN_DATE '
		END	
		
		IF @DATE_TO <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) <= CONVERT(DATETIME,@DATE_TO) '
		END
		ELSE
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) <= @MAX_DATE '
		END		
		
		--SET @SQLSTRING = @SQLSTRING + ' ORDER BY BASE_PRODUCT, PART_NUMBER, SL_DATE_IN DESC,IB.LOT_ID,CAST(WAFER_ID AS INT)'
		--PRINT '@SQLSTRING IS :-'+@SQLSTRING
	
		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PROCESSSTEP_ID = @PROCESSSTEP_ID, @BASEPRODUCT_ID = @BASEPRODUCT_ID,  @DATE_FROM = @DATE_FROM, @DATE_TO = @DATE_TO, @MIN_DATE = @MIN_DATE, @MAX_DATE = @MAX_DATE
 		
		DECLARE @PART_NUMBER 	VARCHAR(50)
		DECLARE @BASE_PRODUCT 	VARCHAR(50)
		DECLARE @LOT_ID 	INT
		DECLARE @WAFER_ID 	INT
		DECLARE @LOT_NUMBER 	VARCHAR(50)
		DECLARE @STEP_ID 	BIGINT
		DECLARE @DATE_IN 	DATETIME
		DECLARE @DATE_OUT 	DATETIME
		DECLARE @BIN1 		INT
		DECLARE @BIN2 		INT
		DECLARE @BIN3 		INT
		DECLARE @BIN4 		INT
		DECLARE @BIN5 		INT 
		DECLARE @CATEGORY	VARCHAR(50)
		DECLARE @RMA_LIMIT	BIT
		DECLARE @MRB_LIMIT	BIT
		DECLARE @COMMENTS	VARCHAR(250)
		DECLARE @BIN_INFO	VARCHAR(20)
		DECLARE @BIN_INFO_STRING VARCHAR(20)
		DECLARE @BIN_INFO_STRING_LENGTH INT
		DECLARE @BIN_VALUE	VARCHAR(10)
		DECLARE @BIN_CURSOR CURSOR

		OPEN RMA_WAFERS
		FETCH NEXT FROM RMA_WAFERS INTO @PART_NUMBER,@BASE_PRODUCT,@LOT_ID,@WAFER_ID,@LOT_NUMBER,@PROCESSSTEP_ID,@DATE_IN,@DATE_OUT,@BIN1,@BIN2,@BIN3,@BIN4,@BIN5,@RMA_LIMIT,@MRB_LIMIT
		WHILE @@FETCH_STATUS = 0
  		BEGIN
			SELECT @CATEGORY = ISNULL(MOVED_TO,'SCRAP'),@COMMENTS = COMMENTS FROM ENGINEERING_LOTS WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID

 			IF @CATEGORY IS NULL OR @CATEGORY = ''
 			BEGIN
				IF ( @RMA_LIMIT = 1 AND @MRB_LIMIT = 0 )
				BEGIN
					SET @CATEGORY = 'MRB'
				END
				ELSE
				BEGIN
					SET @CATEGORY = 'RMA'
				END
 			END

			/*
				If partial quantity of Wafer is released for Assembly (i.e. Only some bins are released for
			the Assembly and Rest of the Bin are under hold, We need to supress the Bin Qty that is release for
			the Assembly while showing up in this report.
			*/
			

			SET @BIN_CURSOR = cursor for
				SELECT ARLT.BIN_INFO FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT WHERE 
					ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND STATUS <> 'Cancelled' AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID

			OPEN @BIN_CURSOR
			FETCH NEXT FROM @BIN_CURSOR INTO @BIN_INFO
			WHILE @@FETCH_STATUS = 0
			BEGIN
				--PRINT '@BIN_INFO'+@BIN_INFO
				SET @BIN_INFO_STRING = REPLACE(UPPER(@BIN_INFO), 'BIN','')
				--PRINT '@BIN_INFO_STRING'+@BIN_INFO_STRING
				
				IF LEN( @BIN_INFO_STRING ) > 0
				BEGIN
					SET @BIN_INFO_STRING = REPLACE(UPPER(@BIN_INFO), 'BIN','')+'&'
					SET @BIN_INFO_STRING_LENGTH = LEN( @BIN_INFO_STRING )
					WHILE ( CHARINDEX('&',@BIN_INFO_STRING) <> 0 )
					BEGIN
						
						SET @BIN_VALUE = SUBSTRING( @BIN_INFO_STRING, 1,CHARINDEX('&',@BIN_INFO_STRING)-1) 
						--PRINT '@BIN_VALUE'+CAST(@BIN_VALUE AS VARCHAR)
						SET @BIN_INFO_STRING = SUBSTRING(@BIN_INFO_STRING, CHARINDEX('&',@BIN_INFO_STRING)+1,LEN(@BIN_INFO_STRING)) 

						IF ( @BIN_VALUE = 1 )
						BEGIN
							SET @BIN1 = 0
						END
						ELSE IF ( @BIN_VALUE = 2 )
						BEGIN
							SET @BIN2 = 0
						END
						ELSE IF ( @BIN_VALUE = 3 )
						BEGIN
							SET @BIN3 = 0
						END
						ELSE IF ( @BIN_VALUE = 4 )
						BEGIN
							SET @BIN4 = 0
						END
						ELSE IF ( @BIN_VALUE = 5 )
						BEGIN
							SET @BIN5 = 0
						END
					END
				END

			FETCH NEXT FROM @BIN_CURSOR INTO @BIN_INFO
			END
			CLOSE @BIN_CURSOR
			DEALLOCATE @BIN_CURSOR
			
			IF ( @BIN1 > 0 OR @BIN2 > 0 OR @BIN3 > 0 OR @BIN4 > 0 OR @BIN5 > 0 ) 
			BEGIN
				INSERT INTO @FINAL_RESULTS (PART_NUMBER,BASE_PRODUCT,LOT_ID,WAFER_ID,LOT_NUMBER,PROCESSSTEP_ID,DATE_IN,DATE_OUT,BIN1,BIN2,BIN3,BIN4,BIN5,CATEGORY,RMA_LIMIT,MRB_LIMIT,COMMENTS)
					VALUES (@PART_NUMBER,@BASE_PRODUCT,@LOT_ID,@WAFER_ID,@LOT_NUMBER,@PROCESSSTEP_ID,@DATE_IN,@DATE_OUT,@BIN1,@BIN2,@BIN3,@BIN4,@BIN5,@CATEGORY,@RMA_LIMIT,@MRB_LIMIT,@COMMENTS)
			END
				SET @PART_NUMBER 	= NULL
				SET @BASE_PRODUCT 	= NULL
				SET @WAFER_ID 		= NULL
				SET @LOT_NUMBER 	= NULL
				SET @PROCESSSTEP_ID 	= NULL
				SET @DATE_IN 		= NULL
				SET @DATE_OUT 		= NULL
				SET @BIN1 		= NULL
				SET @BIN2 		= NULL
				SET @BIN3 		= NULL
				SET @BIN4 		= NULL
				SET @BIN5 		= NULL
				SET @LOT_ID 		= NULL
				SET @CATEGORY 		= NULL
				SET @COMMENTS		= NULL
				SET @RMA_LIMIT		= NULL
				SET @MRB_LIMIT		= NULL

		FETCH NEXT FROM RMA_WAFERS INTO @PART_NUMBER,@BASE_PRODUCT,@LOT_ID,@WAFER_ID,@LOT_NUMBER,@PROCESSSTEP_ID,@DATE_IN,@DATE_OUT,@BIN1,@BIN2,@BIN3,@BIN4,@BIN5,@RMA_LIMIT,@MRB_LIMIT
		END
  		CLOSE RMA_WAFERS
  		DEALLOCATE RMA_WAFERS

	IF @SEL_CATEGORY = '0'
	BEGIN
		SELECT * FROM @FINAL_RESULTS ORDER BY BASE_PRODUCT, PART_NUMBER, DATE_IN DESC, WAFER_ID 
	END
	ELSE
	BEGIN
		SELECT * FROM @FINAL_RESULTS WHERE CATEGORY = @SEL_CATEGORY  ORDER BY BASE_PRODUCT, PART_NUMBER, DATE_IN DESC, WAFER_ID 
	END

 END
